try:
    import openpyxl
    print('Loading openpyxl succeeded')
except ModuleNotFoundError:
    print('For the first time, install the openpyxl library now.')
    os.system('pip install openpyxl')
try:
    import pandas
    print('Loading pandaas succeeded')
except ModuleNotFoundError:
    print('For the first time, install pandas library now.')
    os.system('pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas')
import time
import sys
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl import worksheet
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

wb2 = load_workbook('PO.xlsx',data_only=True)
sheet = wb2.sheetnames
ws2 = wb2[sheet[0]]

#添加首要条件，在发现有隐藏工作表时要求去取消隐藏，以确保目标工作表在首个
for i in wb2.worksheets:
        if i.sheet_state == "hidden":
            print("存在隐藏工作表！")
            input("请删除隐藏工作表后重新启动代码！")
            sys.exit()

#第1行
ws['A1'] = '开料单'
#第3行
ws['G3'] = '半成品'
#第4行
ws['A4'] = '序号'
ws.merge_cells('A4:A5')
ws['B4'] = '存货名称'
ws.merge_cells('B4:B5')
ws['C4'] = '存货分类'
ws.merge_cells('C4:C5')
ws['D4'] = '宽度（宽）'
ws.merge_cells('D4:D5')
ws['E4'] = '高度（长）'
ws.merge_cells('E4:E5')
ws['F4'] = '数量（生产数量）'
ws.merge_cells('F4:F5')
ws['G4'] = '（半成品）物料编码'
ws.merge_cells('G4:G5')
ws['H4'] = '半成品分类'
ws.merge_cells('H4:H5')
ws['I4'] = '表面处理（自由项）'
ws.merge_cells('I4:I5')
ws['J4'] = '色号（自由项）'
ws.merge_cells('J4:J5')
ws['K4'] = '（半成品）物料名称'
ws.merge_cells('K4:K5')
ws['L4'] = '规格型号'
ws.merge_cells('L4:L5')
ws['M4'] = '企料尺寸(高度)长'
ws.merge_cells('M4:M5')
ws['N4'] = '横料尺寸（宽度）宽'
ws.merge_cells('N4:N5')
ws['O4'] = '制作批量'
ws.merge_cells('O4:O5')
ws['P4'] = '半成品单位'
ws.merge_cells('P4:P5')
ws['Q4'] = '位置'
ws['R4'] = '加工图信息'
ws['S4'] = '存货属性'
ws['T4'] = '加工参数'
ws['AN4'] = '立面图（外看外开）'
ws['BH4'] = '立面图（外看外开）'
ws.merge_cells('BH4:BK4')
ws['BM4'] = '物料集（BOM）'
ws.merge_cells('BM4:BV4')
ws['BW4'] = '设备集'
ws.merge_cells('BW4:BX4')
ws['CD4'] = '计量组名称'
ws['CE4'] = '主计量名称'
ws['CF4'] = '辅计量名称'
ws['CG4'] = '采购计量名称'
ws['CH4'] = '销售计量名称'
ws['CI4'] = '发货计量名称'
ws['CJ4'] = '库存计量名称'
ws['CK4'] = '生产计量名称'
ws['CL4'] = '成本计量名称'
ws['CM4'] = '拼装图号'
ws['CN4'] = '加工单号'
ws['CO4'] = '图纸链接'
#第5行
ws['R5'] = '图号（图号）'
ws['T5'] = 'A1'
#中间暂时省略
ws['BH5'] = '开料'
ws['BI5'] = '钻铣'
ws['BJ5'] = '拼装'
ws['BK5'] = '注胶'
ws['BL5'] = '包装'
ws['BM5'] = '物料名称'
ws['BN5'] = '物料编码'
ws['BO5'] = '编码（自由项）'
ws['BP5'] = '规格'
ws['BQ5'] = '色号（自由项）'
ws['BR5'] = '宽度（宽）'
ws['BS5'] = '高度（长）'
ws['BT5'] = '材料用量'
ws['BU5'] = '计量单位'
ws['BV5'] = '工序'
ws['BW5'] = '设备名称'
ws['BX5'] = '工序'
ws['BY5'] = '标准作业时间'
ws['BZ5'] = '准备时间'
ws['CA5'] = '搬运时间'
ws['CB5'] = '工序'
ws['CC5'] = '工作车间'
ws['CM5'] = '拼装图号'
ws['CN5'] = '加工单价'
ws['CO5'] = '图纸链接'

#第6行
ws['CD6'] = '平方单位'
ws['CE6'] = '平方米'
ws['CF6'] = '件'
ws['CG6'] = '平方米'
ws['CH6'] = '平方米'
ws['CI6'] = '平方米'
ws['CJ6'] = '平方米'
ws['CK6'] = '件'
ws['CL6'] = '平方米'
ws['CN6'] = '（单价请输入）'


'''str1 = 'A'
U = 6
str2 = repr(U)
str3 = str1 + str2
ws[str3] = '行不行！'''

'''
#设置单元格格式
alignment_center = Alignment(horizontal='center', vertical='center')
ws_area = ws['A1:CN6']
for i in ws_area:
	for j in i:
		j.alignment = alignment_center'''

print(wb2.active.title)


def First():
    A = 0
    B = 6
    for row in ws2.iter_rows(min_row=B, max_row=30000, min_col=4, max_col=4):
        for cell in row:
            if str(cell.value).find('P') != -1:
                A = cell.row
                D = cell.row
                ws.cell(row=D , column=2).value = cell.value
                print(A)
                C = cell.value
                print(cell.value)
                for row in ws2.iter_rows(min_row=A+1, max_row=30000, min_col=4, max_col=4):
                    for cell in row:
                        if str(cell.value).find('P') != -1:
                            B = cell.row
                            print(B)
                            for i in range(A,B):
                                ws.cell(row=i , column=1).value = C
First()
                            #此处获得行数，应加上配件行数，键入对应于第一列的行数编号
'''                         for row in ws.iter_rows(min_row=A, max_row=B-1, min_col=1, max_col=1):
                                for cell in row:
                                    if cell.row < B:

                            for row in ws.iter_rows(min_row=A, max_row=B-1, min_col=9, max_col=9):
                                for cell in row:
                                    if cell.value != None:
                                        #将单元值逐个录入数据本中
                                        #检查数据本的长度X
                                        #开始在行B的地方写入长度为X的数据
                                        #retuen X
                            for row in ws.iter_rows(min_row=A, max_row=B-1, min_col=7, max_col=7):
                                for cell in row:
                                    if str(cell.value).find('玻璃') != -1:
                                        #将单元值逐个录入数据本中
                                        #检查数据本的长度Y
                                        #开始在行B+X的地方写入长度为Y的数据
                                        #return Y
                            for row in ws.iter_rows(min_row=A, max_row=B-1, min_col=7, max_col=7):
                                for cell in row:
                                    if str(cell.value).find('镀锌') != -1 and str(cell.value).find('板') != -1:
                                        #将单元值逐个录入数据本中
                                        #检查数据本的长度Y
                                        #开始在行B+X+Y的地方写入长度为Z的数据
                                        #return Z
                            for row in ws.iter_rows(min_row=A, max_row=B-1, min_col=7, max_col=7):
                                for cell in row:
                                    if str(cell.value).find('铝') != -1 and str(cell.value).find('板') != -1:
                                        #将单元值逐个录入数据本中
                                        #检查数据本的长度XX
                                        #开始在行B+X+Y+Z的地方写入长度为XX的数据
                                        #return XX
                            #判断B-A是否等于B+X+Y+Z+XX
                            #其中变量通过str相加，转为单元格坐标范围'''



print('最大行数:')
print(ws.max_row)

wb.save('Mest.xlsx')
#保存文件并命名
wb.close()
#关闭表格文件

input('请输入任意按键退出')


#Log:
#Ver:1.0
#Date:2022.11.23
#Update content:1.New Features:   1)Determine whether there are hidden worksheets

#Write by r11-s9edn5hhl